﻿-- =============================================
-- Author:		Jon Stocksdale
-- Create date: May 3, 2016
-- Description:	Return Start or End Dates for a particular TimeDiv
-- PASS:
--     Paramter1:  ShortTimeDivName OR CURRENT.timedivtype  (ie CURRENT.SchoolYear or CURRENT.Term)
--     Paramter2:  START or END
--   This means two calls will be necessary to get both.
-- =============================================
CREATE FUNCTION [dbo].[GetTimeDivDate] 
(
	@TimeDiv nvarchar(50),
	@ReturnWhat nvarchar(20),
	@UserEntityID int = 0,
	@InstitutionID int = 0
)
RETURNS DateTime
AS
BEGIN

	DECLARE @STARTDATE DateTime, @ENDDATE DateTime, @RETDATE DateTime, @SEQUENCE int
	
	If (SUBSTRING(@TimeDiv,0,8) = 'CURRENT') BEGIN
		SELECT @STARTDATE=StartDate, @ENDDATE=EndDate FROM dbo.TimeDivisions WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME() AND TimeDivType = SUBSTRING(@TimeDiv,9,100)
	END
	ELSE If (SUBSTRING(@TimeDiv,0,5) = 'NEXT') BEGIN
		SELECT @SEQUENCE=Sequence FROM
			(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, TimeDivID, TimeDivType, ShortName, StartDate, EndDate, InstitutionEntityID, SuperTimeDivID, Name, Description, ExternalID
				FROM TimeDivisions) AS X
				 WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME() AND TimeDivType = SUBSTRING(@TimeDiv,6,100)
		SELECT @STARTDATE=StartDate, @ENDDATE=EndDate FROM
				(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, TimeDivID, TimeDivType, ShortName, StartDate, EndDate, InstitutionEntityID, SuperTimeDivID, Name, Description, ExternalID
				FROM TimeDivisions) AS X
				 WHERE Sequence=@SEQUENCE+1 AND TimeDivType = SUBSTRING(@TimeDiv,6,100)
	END
	ELSE If (SUBSTRING(@TimeDiv,0,5) = 'PREV') BEGIN
		SELECT @SEQUENCE=Sequence FROM
			(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, TimeDivID, TimeDivType, ShortName, StartDate, EndDate, InstitutionEntityID, SuperTimeDivID, Name, Description, ExternalID
				FROM TimeDivisions) AS X
				 WHERE StartDate < SYSDATETIME() AND EndDate > SYSDATETIME() AND TimeDivType = SUBSTRING(@TimeDiv,6,100)
		SELECT @STARTDATE=StartDate, @ENDDATE=EndDate FROM
				(
				SELECT ROW_NUMBER() OVER(Partition By TimeDivType Order By StartDate) AS Sequence, TimeDivID, TimeDivType, ShortName, StartDate, EndDate, InstitutionEntityID, SuperTimeDivID, Name, Description, ExternalID
				FROM TimeDivisions) AS X
				 WHERE Sequence=@SEQUENCE-1 AND TimeDivType = SUBSTRING(@TimeDiv,6,100)
	END
	ELSE BEGIN
			SELECT @STARTDATE=StartDate, @ENDDATE=EndDate FROM dbo.TimeDivisions WHERE ShortName = @TimeDiv
	END
	
	IF (@ReturnWhat = 'START') BEGIN
		SET @RETDATE = @STARTDATE
	END
	ELSE BEGIN
		SET @RETDATE = @ENDDATE
	END
	RETURN @RETDATE
END